1. Field of the Invention
The present invention generally relates to data processing and more particularly to utilizing logical fields with conditional modifiers in abstract queries.
2. Description of the Related Art
Databases are computerized information storage and retrieval systems. A relational database management system is a computer database management system (DBMS) that uses relational techniques for storing and retrieving data. The most prevalent type of database is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Regardless of the particular architecture, in a DBMS, a requesting entity (e.g., an application or the operating system) demands access to a specified database by issuing a database access request. Such requests may include, for instance, simple catalog lookup requests or transactions and combinations of transactions that operate to read, change and add specified records in the database. These requests are made using high-level query languages such as the Structured Query Language (SQL) and application programming interfaces (API's) such as Java® Database Connectivity (JDBC). The term “query” denominates a set of commands for retrieving data from a stored database. Queries take the form of a command language, such as SQL, that lets programmers and programs select, insert, update, find out the location of data, and so forth.
Unfortunately, generating queries using SQL may require a detailed understanding of the possibly complex physical layout of the underlying database and interpretation of cryptic field names. For some applications, to facilitate the query building process, an abstraction model may be utilized that, in effect, hides some of the complexities of the underlying database physical layout from users. The abstraction model may include logical fields with recognizable names that map to corresponding physical fields of the underlying database. “Abstract” queries may be generated containing conditions based on the logical fields. Upon issuance, the logical fields of an abstract query may be mapped to corresponding physical fields to create a physical or “concrete” query. For some applications, abstract queries may be saved, allowing subsequent users to reuse the saved abstract queries without having to generate their own. The concepts of data abstraction and abstract queries are described in detail in the commonly owned, co-pending application Ser. No. 10/083,075, entitled “Improved Application Portability And Extensibility Through Database Schema And Query Abstraction,” filed Feb. 26, 2002, herein incorporated by reference in its entirety.
Abstract queries often contain a conditional element (e.g., a WHERE clause in a corresponding SQL statement) in which a logical field is used within an expression with a comparison operator. As described in the related application Ser. No. 10/083,075, logical fields are typically mapped to a particular physical field in a physical database. For instance, if the physical database was implemented as a relational database, a particular logical field would be mapped to a particular column within a relational table.
While abstraction makes working with the data easy and efficient, in certain instances, there remains a need to make logical fields flexible so that they can refer to more than one field in a physical database. For example, suppose that in an environment supporting a medical testing application, a logical field named WEIGHT is mapped to a column named “weight” in a relational table named demographic. Consider that the weight column contains patients' most recently measured weight. Accordingly, in abstract queries where a patient's current weight is required, the WEIGHT logical field can be utilized. For example, in order to determine the names of all patients who currently weigh more than 220 pounds, the following abstract query may be submitted: Return NAME where WEIGHT>220.
Further, suppose that patients' weights measured in previous years (i.e., historical weight) is also available in the database. However, the historical data is not available in the demographic table, but rather in a separate table called weight_history. A logical field called HISTORICAL_WEIGHT is mapped to the weight column of weight_history table. Therefore, the names of all patients who have weighed more than 220 in the past can be found via the following query: Return NAME where HISTORICAL_WEIGHT>220.
In terms of the above example queries, if the user wanted to consolidate the two sets of query results pertaining to current patient weight data and historical patient weight data, they would have to run two separate queries (as shown above) and then merge the query results.
Further, it should be recognized that the WEIGHT and the WEIGHT_HISTORY logical fields represent different perspectives of the same data. For example, the WEIGHT logical field represents current data regarding patients' weights, and the WEIGHT_HISTORY logical field represents historical data. In many cases, it would be beneficial to provide users the ability to gain access to multiple physical fields via a single logical field. For instance, in the above example, it would be beneficial to provide users access to both the current data and the historical data via a single logical field, such as the WEIGHT logical field. Unfortunately, the infrastructure facilitating existing data abstraction models may not be flexible enough to allow for the referencing of multiple physical data sources as described above. Therefore, what is needed is a data abstraction solution that allows for the referencing of multiple physical fields via a single logical field. A further need is to provide users the ability to specify, on an instance by instance basis, which physical field or combination of physical fields a particular logical field should reference.